package com.symria.server.dao.common;

import java.util.Date;
import java.util.List;

import com.symria.framework.dao.util.TemplateDao;
import com.symria.framework.page.Page;
import com.symria.framework.util.DateUtil;
import com.symria.framework.util.NumberUtil;
import com.symria.server.po.AdAppReport;
import com.symria.server.po.AdCampaign;
import com.symria.server.po.AppDateReport;
import com.symria.server.po.AppInfo;
import com.symria.server.po.CustomInfo;
import com.symria.server.po.PackDateReport;
import com.symria.server.po.PackInfo;
import com.symria.server.po.SysAreaInfo;
import com.symria.server.po.UserInfo;
import com.symria.system.constants.ATTAdStatus;
import com.symria.system.constants.ATTSystem;
import com.symria.system.constants.AppStatus;
import com.symria.system.statement.Statement;


public class CampaignDao extends TemplateDao {

	public void searchAdCampaign(AdCampaign ad,String viewType,Page page){
		Statement stms = stmsFactory.createStatement("select ad,ac from AdCampaign ad,AdContent ac where ad.adId=ac.adId");
		          if(ad!=null){
		        	  stms.append("and", "ad.status", "=", ad.getStatus());
		        	  stms.appendBetween("and", "ad.createDate", ad.getBeginDate(), ad.getEndDate());
		        	  stms.appendLike(null, "and", "ad.adName", ad.getAdName());
		        	  stms.append("and", "ad.adCate", "=", ad.getAdCate());
		          }
		          stms.append("and", "ac.viewType", "=", viewType);
		          stms.addOrderBy(" ad.createDate desc");
		          templateDao.search(stms, page);
	}
	public List<SysAreaInfo> queryAreaInfo() {
		Statement stms = stmsFactory.createStatement(" from SysAreaInfo where isCapital=?");
		          stms.addParam(ATTSystem.LOGIC_TRUE);
		return templateDao.query(stms);
	} 
	public void deleteOperator(Integer adId){
		Statement stms = stmsFactory.createNativeStatement("DELETE o FROM AD_OPERATOR o WHERE o.AD_ID=?");
		          stms.addParam(adId);
		          templateDao.update(stms);
	}
	public void deleteAdArea(Integer adId){
		Statement stms = stmsFactory.createNativeStatement("DELETE o FROM AD_AREA o WHERE o.AD_ID=?");
		          stms.addParam(adId);
		          templateDao.update(stms);
	}
	public List<Integer> queryAreaIdByadId(Integer adId){
		Statement stms = stmsFactory.createNativeStatement("SELECT a.AREA_ID FROM AD_AREA a WHERE a.AD_ID=?");
		          stms.addParam(adId);
		return templateDao.query(stms);
	}
	public List<String> queryAdOperateByadId(Integer adId){
		Statement stms = stmsFactory.createNativeStatement("SELECT ao.OPERATOR FROM AD_OPERATOR ao WHERE ao.AD_ID=?");
		          stms.addParam(adId);
		return templateDao.query(stms);
	}
	public void searchReport(AdAppReport aar,Date beginDate,Date endDate,Page page){
		Statement stms = stmsFactory.createStatement(" from AdAppReport aar where 1=1");
				if(null!=aar){
					stms.append("and", "aar.appId", "=", aar.getAppId());
					stms.append("and", "aar.adId", "=", aar.getAdId());
					stms.append("and", "aar.hour", "=", aar.getHour());
					stms.appendBetween("and", "aar.createTime", beginDate, endDate);
					
				}
				stms.addOrderBy("aar.createTime desc,aar.hour desc ");
				
				
		templateDao.search(stms, page);
		          
	}
	public Object[] finedSumCount(AdAppReport aar,Date beginDate,Date endDate){
		Statement stms = stmsFactory.createNativeStatement("SELECT SUM(aar.REQUEST_COUNT),SUM(aar.SHOW_COUNT),SUM(aar.CLICK_COUNT),SUM(aar.DOWN_COUNT),SUM(aar.ACTION_COUNT),SUM(aar.GET_MONEY),SUM(aar.PAY_MONEY) FROM AD_APP_REPORT aar where 1=1 ");
					if(null!=aar){
						//stms.append("and", "aar.PUBLIC_ID", "=", aar.getPublicId());
						stms.append("and", "aar.AD_ID", "=", aar.getAdId());
						stms.appendBetween("and", "aar.CREATE_TIME", beginDate, endDate);
					}
		return (Object[])templateDao.find(stms);
	}
	public void dailyReport(Date beginDate,Date endDate,Page page){
		Statement stms = stmsFactory.createNativeStatement(" SELECT aar.CREATE_TIME,SUM(aar.REQUEST_COUNT),SUM(aar.SHOW_COUNT),SUM(aar.CLICK_COUNT),SUM(aar.DOWN_COUNT),SUM(aar.ACTION_COUNT),SUM(aar.GET_MONEY),SUM(aar.PAY_MONEY) FROM AD_APP_REPORT aar  where 1=1 ");
				  stms.appendBetween("and", "aar.CREATE_TIME", beginDate, endDate);
				  stms.addGroupBy(" aar.CREATE_TIME");
				  stms.addOrderBy(" aar.CREATE_TIME desc ");
		templateDao.search(stms, page);
	}
	public void campaignReport(AdAppReport aar,Date beginDate,Date endDate,Page page){
		  Statement stms = stmsFactory.createNativeStatement(" SELECT aar.AD_ID,ac.AD_NAME,aar.CREATE_TIME,SUM(aar.REQUEST_COUNT) rc,SUM(aar.SHOW_COUNT),SUM(aar.CLICK_COUNT),SUM(aar.DOWN_COUNT),SUM(aar.ACTION_COUNT),SUM(aar.GET_MONEY),SUM(aar.PAY_MONEY) FROM AD_APP_REPORT aar ,AD_CAMPAIGN ac where aar.AD_ID=ac.AD_ID  ");
				  if(null!=aar){
						//stms.append("and", "aar.PUBLIC_ID", "=", aar.getPublicId());
						stms.append("and", "aar.AD_ID", "=", aar.getAdId());
						stms.appendBetween("and", "aar.CREATE_TIME", beginDate, endDate);
				   }  
		
		  stms.appendBetween("and", "aar.CREATE_TIME", beginDate, endDate);
		  stms.addGroupBy(" aar.AD_ID,aar.CREATE_TIME");
		  stms.addOrderBy(" aar.CREATE_TIME desc, rc desc");
		  templateDao.search(stms, page);
		
	}
	public void searchPush(Page page){
		Statement stms = stmsFactory.createStatement("select p from AdPush  p,AdCampaign ad where p.adId=ad.adId and ad.status=?");
		          stms.addParam(ATTAdStatus.STATUS_CHE.toString());
		          stms.addOrderBy(" p.adId desc");
		  templateDao.search(stms, page);
	}
	public Integer searchPushLevel(){
		Statement stms = stmsFactory.createNativeStatement("SELECT IFNULL(SUM(p.LEVEL),0) FROM AD_PUSH p,AD_CAMPAIGN ac WHERE p.AD_ID=ac.AD_ID AND ac.STATUS=?");
		          stms.addParam(ATTAdStatus.STATUS_CHE.toString());
		return   NumberUtil.getInteger(templateDao.find(stms), 0);
		
	}
	public void searchApp(AppInfo ap,Date beginDate,Date endDate,Page page){
		Statement stms = stmsFactory.createStatement("from AppInfo ai where 1=1");
		if(ap!=null){
			stms.appendLike(null, "and", "ai.appName", ap.getAppName());
			stms.append("and", "ai.appPlat", "=", ap.getAppPlat());
			stms.append("and", "ai.status", "=", ap.getStatus());
			stms.append("and", "ai.appCate", "=", ap.getAppCate());
			stms.appendBetween("and", "ai.createDate", beginDate, endDate);
		}
		stms.append("and", "ai.status", "!=", AppStatus.STATUS_DEL.toString());
		stms.addOrderBy(" ai.appId desc ");
		  templateDao.search(stms, page);        
	}
	public void appReport(AppDateReport adr,Date beginDate,Date endDate,Page page){
		Statement stms = stmsFactory.createStatement(" from AppDateReport adr where 1=1");
		          if(adr!=null){
		        	  stms.append("and", "adr.appId", "=", adr.getAppId());
			          stms.appendLike(null, "and", "adr.appName", adr.getAppName());
			          stms.appendBetween("and", "aar.CREATE_TIME", beginDate, endDate);  
		          } 
		          stms.addOrderBy("adr.createTime desc");
        templateDao.search(stms, page);
	}
	public void searchImei(String orderBy,String imei,Date createDate,Date lastDate,Page page){
		Statement stms = stmsFactory.createStatement("from ImeiInfo where 1=1");
		          stms.appendLike(null, "and", "imei", imei);
		          stms.appendBetween("and", "createDate", null, createDate);
		          stms.appendBetween("and", "createDate", null, lastDate);
		          if("request".equals(orderBy)){
		        	  stms.addOrderBy(" requestCount desc");
		          }else if("show".equals(orderBy)){
		        	  stms.addOrderBy(" showCount desc");
		          }else if("click".equals(orderBy)){
		        	  stms.addOrderBy(" clickCount desc");
		          }else if("down".equals(orderBy)){
		        	  stms.addOrderBy(" actionCount desc");
		          }else{
		        	  stms.addOrderBy(" lastDate desc");
		          }
		          templateDao.search(stms, page);
		
	}
	public void searchPackInfo(PackInfo pack,Page page){
		 Statement stms = stmsFactory.createStatement("from PackInfo p where 1=1 ");
					if(pack!=null){
						 stms.appendLike(null, "and", "p.packName", pack.getPackName());
						 stms.appendLike(null, "and", "p.packOwner", pack.getPackOwner());
						 stms.appendLike(null, "and", "p.channelName", pack.getChannelName());
						 stms.append("and","p.state", "=", pack.getState());	 
					}
		           stms.addOrderBy("p.id desc");
		           templateDao.search(stms, page);
	}
	public void saveOrUpdatePackDateReport(PackDateReport pdr){
		Statement stms = stmsFactory.createNativeStatement("INSERT  INTO `PACK_DATE_REPORT`(`CHANNEl_ID`,`PACK_ID`,`ACTION_COUNT`,`FACT_COUNT`,`CREATE_DATE`,`PACK_PRICE`,`FACT_MONEY`,`STATE`)");
		          stms.append("values ("+pdr.getChannelId()+","+pdr.getPackId()+","+pdr.getActionCount()+","+pdr.getFactCount()+",'"+DateUtil.getFormatDate(pdr.getCreateDate(), "yyyy-MM-dd")+"',"+pdr.getPackPrice()+","+pdr.getFactMoney()+",'"+pdr.getState()+"')");
                  stms.append(" ON DUPLICATE KEY UPDATE ACTION_COUNT="+pdr.getActionCount() +" ,FACT_COUNT="+pdr.getFactCount()+" ,PACK_PRICE="+pdr.getPackPrice()+" ,FACT_MONEY="+pdr.getFactMoney());
		          templateDao.update(stms);
	}
	public void searchPackReport(PackDateReport pdr,Date beginDate,Date endDate,String state,Page page){
	   Statement stms = stmsFactory.createStatement("select pdr from PackDateReport pdr where 1=1 ");
	             if(pdr!=null){
	                stms.append("and", "pdr.channelId", "=", pdr.getChannelId());
	                stms.append("and", "pdr.packId", "=", pdr.getPackId());
	                stms.appendLike(null, "and", "pdr.channelName", pdr.getChannelName());
	                stms.appendLike(null, "and", "pdr.packName", pdr.getPackName());
	             }
	             stms.append("and", "pdr.state", "=",state);

	             stms.appendBetween("and", "pdr.createDate", beginDate, endDate);	
	             stms.addOrderBy(" pdr.createDate desc");
	             templateDao.search(stms, page);
	   
	}
	public void searchDailyReport(Page page,Date beginDate,Date endDate){
		Statement stms = stmsFactory.createStatement(" from DailyReport dr where 1=1");
		          stms.appendBetween("and", "dr.createDate", beginDate, endDate);
		          stms.addOrderBy("dr.createDate desc");
		templateDao.search(stms, page);
	}
	public List<UserInfo> queryChannel(){
	    Statement stms = stmsFactory.createStatement(" from UserInfo u where u.roleId=? and u.status=?");
	              stms.addParam(ATTSystem.CHANNEL_ROLE_ID);
	              stms.addParam(ATTSystem.LOGIC_TRUE);
	              stms.addOrderBy(" u.userId desc");
	   return  templateDao.query(stms);
	}
	public List<PackInfo> queryPackInfo(){
	    Statement stms = stmsFactory.createStatement(" from PackInfo p where p.state=? ");
	              stms.addParam(ATTSystem.LOGIC_TRUE);
	   return  templateDao.query(stms);
	}
	//0:packId 1:packName 2:createDate 3:actionCount 4:factCount 5:price 6:payPrice 7:getMoney 8:payMoney 9:money
	public void searchPackDateReportByDate(Date beginDate,Date endDate,Integer packId,String packName,Integer channelId, Page page){
		//Statement stms = stmsFactory.createNativeStatement("SELECT SUM(pdr.ACTION_COUNT) AS ac ,SUM(pdr.FACT_COUNT) AS fc, SUM(p.PACK_PRICE*pdr.ACTION_COUNT) AS gm, SUM(pdr.PACK_PRICE*pdr.FACT_COUNT) AS pm,pdr.CREATE_DATE,(SUM(p.PACK_PRICE*pdr.ACTION_COUNT)-SUM(pdr.PACK_PRICE*pdr.FACT_COUNT) ) AS pgm  FROM PACK_DATE_REPORT pdr,PACK_INFO p WHERE pdr.PACK_ID=p.ID AND pdr.STATE=? ");
		  Statement stms = stmsFactory.createNativeStatement(" SELECT ");
                    stms.append(" pdr.PACK_ID,");
                    stms.append(" p.PACK_NAME,");
                    stms.append(" pdr.CREATE_DATE,");
                    stms.append(" pdr.ACTION_COUNT,");
                    stms.append(" pdr.FACT_COUNT,");
                    stms.append(" p.PACK_PRICE,");
                    stms.append(" pdr.PACK_PRICE,");
                    stms.append(" p.PACK_PRICE*pdr.ACTION_COUNT, ");
                    stms.append(" pdr.PACK_PRICE*pdr.FACT_COUNT,");
                    stms.append(" CASE WHEN pdr.CHANNEL_ID=? THEN p.PACK_PRICE*pdr.ACTION_COUNT ELSE p.PACK_PRICE*pdr.ACTION_COUNT-pdr.PACK_PRICE*pdr.FACT_COUNT END AS mey");
                    stms.append(" FROM PACK_DATE_REPORT pdr,PACK_INFO p WHERE pdr.PACK_ID=p.ID AND pdr.STATE=? ");
		            stms.addParam(ATTSystem.HENG_TONG_CHANNEL_ID);
                    stms.addParam(ATTSystem.LOGIC_TRUE);
		            stms.appendBetween("and", "pdr.CREATE_DATE", beginDate, endDate);
		            stms.append("AND", "pdr.PACK_ID", "=", packId);
		            stms.append("AND", "pdr.CHANNEL_ID", "=", channelId);
		            stms.appendLike(null, "AND", "p.PACK_NAME", packName);
		            stms.addOrderBy(" pdr.PACK_ID DESC,pdr.CREATE_DATE DESC");
		            templateDao.search(stms, page);
	}
	
	public void searchPackDateReportBySum(Date beginDate,Date endDate,Integer packId,String packName,Integer channelId, Page page){
		Statement stms = stmsFactory.createNativeStatement("SELECT pdr.PACK_ID,p.PACK_NAME,SUM(pdr.ACTION_COUNT),SUM(pdr.FACT_COUNT),SUM(p.PACK_PRICE*pdr.ACTION_COUNT),SUM(pdr.PACK_PRICE*pdr.FACT_COUNT) FROM PACK_DATE_REPORT pdr,PACK_INFO p WHERE p.ID=pdr.PACK_ID AND pdr.STATE=? ");
	              stms.addParam(ATTSystem.LOGIC_TRUE);
	              stms.appendBetween("and", "pdr.CREATE_DATE", beginDate, endDate);
		          stms.append("AND", "pdr.PACK_ID", "=", packId);
		          stms.append("AND", "pdr.CHANNEL_ID", "=", channelId);
		          stms.appendLike(null, "AND", "p.PACK_NAME", packName);
	              stms.addOrderBy(" pdr.PACK_ID DESC");
	              templateDao.search(stms, page);
	}
	public List<Integer> searchPackId(Integer channelId,Date beginDate,Date endDate){
		Statement stms = stmsFactory.createNativeStatement("SELECT DISTINCT pdr.PACK_ID FROM PACK_DATE_REPORT pdr,PACK_INFO p WHERE p.ID=pdr.PACK_ID AND pdr.STATE=? ");//AND p.STATE=?
	              stms.addParam(ATTSystem.LOGIC_TRUE);
	              //stms.addParam(ATTSystem.LOGIC_TRUE);
	              stms.appendBetween("and", "pdr.CREATE_DATE", beginDate, endDate);
	              stms.append("AND", "pdr.CHANNEL_ID", "=", channelId);
		          stms.addOrderBy(" pdr.PACK_ID DESC");
		          return templateDao.query(stms);
	              
	}
	//0:packId 1:packName 2:createDate 3:actionCount 4:factCount 5:price 6:payPrice 7:getMoney 8:payMoney 9:money 10:channelId
	public List<Object[]> queryPackDateReportByPackId(Integer packId,Integer channelId,Date beginDate,Date endDate){
		 Statement stms = stmsFactory.createNativeStatement(" SELECT ");
         stms.append(" pdr.PACK_ID,");
         stms.append(" p.PACK_NAME,");
         stms.append(" pdr.CREATE_DATE,");
         stms.append(" pdr.ACTION_COUNT,");
         stms.append(" pdr.FACT_COUNT,");
         stms.append(" p.PACK_PRICE as get,");
         stms.append(" pdr.PACK_PRICE as pay,");
         stms.append(" p.PACK_PRICE*pdr.ACTION_COUNT, ");
         stms.append(" CASE WHEN pdr.CHANNEL_ID=? THEN 0  WHEN pdr.CHANNEL_ID=? THEN 0  ELSE pdr.PACK_PRICE*pdr.FACT_COUNT END,");
         //stms.append(" pdr.PACK_PRICE*pdr.FACT_COUNT,");
         stms.append(" CASE WHEN pdr.CHANNEL_ID=? THEN p.PACK_PRICE*pdr.ACTION_COUNT  WHEN pdr.CHANNEL_ID=? THEN p.PACK_PRICE*pdr.ACTION_COUNT  ELSE p.PACK_PRICE*pdr.ACTION_COUNT-pdr.PACK_PRICE*pdr.FACT_COUNT END AS mey ,");
         stms.append(" pdr.CHANNEL_ID");
         stms.append(" FROM PACK_DATE_REPORT pdr,PACK_INFO p WHERE pdr.PACK_ID=p.ID AND pdr.STATE=? ");
         stms.addParam(ATTSystem.HENG_TONG_CHANNEL_ID);
         stms.addParam(ATTSystem.FEI_YING_CHANNEL_ID);
         stms.addParam(ATTSystem.HENG_TONG_CHANNEL_ID);
         stms.addParam(ATTSystem.FEI_YING_CHANNEL_ID);
         stms.addParam(ATTSystem.LOGIC_TRUE);
         stms.appendBetween("and", "pdr.CREATE_DATE", beginDate, endDate);
         stms.append("AND", "pdr.PACK_ID", "=", packId);
         stms.append("AND", "pdr.CHANNEL_ID", "=", channelId);
         stms.addOrderBy(" pdr.PACK_ID DESC,pdr.CREATE_DATE DESC");
         return templateDao.query(stms);
         
	}
    public List<CustomInfo> queryCustomInfo(){
    	Statement stms = stmsFactory.createStatement(" from CustomInfo where 1=1");
    	return templateDao.query(stms);
    }
	//0 realName 1 channelId 2packId 3packName 4packPrice 5factCount 6money
	public void searchPackReportByChannel(Date beginDate,Date endDate,Integer packId,Integer channelId,Page page,String total){ 
		          if(com.symria.framework.util.StringUtil.equals(ATTSystem.LOGIC_TRUE, total)){
			              Statement stms = stmsFactory.createNativeStatement("SELECT u.REAL_NAME,u.USER_ID,p.PACK_NAME,pdr.PACK_ID, pdr.PACK_PRICE,SUM(pdr.FACT_COUNT),SUM(pdr.FACT_MONEY) FROM PACK_DATE_REPORT pdr,PACK_INFO p,USER_INFO u WHERE pdr.PACK_ID=p.ID AND u.USER_ID=pdr.CHANNEL_ID");
				          stms.appendBetween("and", "pdr.CREATE_DATE", beginDate, endDate);
				          stms.append("and", "u.USER_ID", "=", channelId);
				          stms.append("and", "pdr.PACK_ID", "=", packId);
			        	  stms.addGroupBy("pdr.PACK_ID,u.USER_ID");
			        	  templateDao.search(stms, page);
		          }else{
			        	 // Statement stms = stmsFactory.createNativeStatement("SELECT u.REAL_NAME,u.USER_ID,p.PACK_NAME,pdr.PACK_ID, pdr.PACK_PRICE,SUM(pdr.FACT_COUNT),SUM(pdr.FACT_COUNT)*pdr.PACK_PRICE FROM PACK_DATE_REPORT pdr,PACK_INFO p,USER_INFO u WHERE pdr.PACK_ID=p.ID AND u.USER_ID=p.CHANNEL_ID");
			        	  Statement stms = stmsFactory.createNativeStatement("SELECT u.REAL_NAME,u.USER_ID,SUM(pdr.FACT_COUNT),SUM(pdr.FACT_MONEY)  FROM PACK_DATE_REPORT pdr,PACK_INFO p,USER_INFO u WHERE pdr.PACK_ID=p.ID AND u.USER_ID=pdr.CHANNEL_ID ");
				          stms.appendBetween("and", "pdr.CREATE_DATE", beginDate, endDate);
				          stms.append("and", "u.USER_ID", "=", channelId);
				          stms.append("and", "pdr.PACK_ID", "=", packId);
			        	  stms.addGroupBy("u.USER_ID");
			        	  templateDao.search(stms, page);
		          }
	}
	
	public void searchPackReportByCustom(Date beginDate,Date endDate,Integer packId,Integer customId,Page page,String total){
		Statement stms = stmsFactory.createNativeStatement("SELECT c.COM_NAME,p.PACK_NAME,p.ID,p.PACK_PRICE,SUM(pdr.FACT_COUNT),SUM(pdr.FACT_COUNT)*p.PACK_PRICE,c.ID as cid FROM PACK_DATE_REPORT pdr,PACK_INFO p,CUSTOM_INFO c WHERE pdr.PACK_ID=p.ID AND c.ID=p.CUSTOM_ID ");
        stms.appendBetween("and", "pdr.CREATE_DATE", beginDate, endDate);
        stms.append("and", "p.CUSTOM_ID", "=", customId);
        stms.append("and", "pdr.PACK_ID", "=", packId);
        if(com.symria.framework.util.StringUtil.equals(ATTSystem.LOGIC_TRUE, total)){
        	stms.addGroupBy(" c.ID,p.ID");
        }else{
        	stms.addGroupBy(" c.ID");	
        }
        templateDao.search(stms, page);
	}
}
